<?php

class Mysql
{
    const JOIN_TYPE_INNER = 'INNER';
    const JOIN_TYPE_LEFT = 'LEFT';
    const JOIN_TYPE_RIGHT = 'RIGHT';
    const INSERT_IGNORE = 'IGNORE';
    const SELECT = 'SELECT %1$s FROM %2$s';
    const JOIN = '%1$s JOIN %2$s';
    const WHERE = 'WHERE %1$s';
    const JOIN_ON = 'ON %1$s';
    const INSERT = 'INSERT %1$s INTO %2$s(%3$s) VALUES%4$s';
    const UPDATE = 'UPDATE %1$s SET %2$s';
    const DELETE = 'DELETE FROM %1$s';
    const INSERT_UPDATE = 'ON DUPLICATE KEY UPDATE %1$s';
    private $sqlFormar = array(
        'group' => 'GROUP BY %1$s',
        'order' => 'ORDER BY %1$s',
        'limit' => 'LIMIT %1$s',
        'offset' => 'OFFSET %1$s',
    );
    public $sqlLog;
    public static $db;
    public $where;
    public $dbConfig;
    public $offset;
    public $limit;
    public $order;
    public $columns = array('*');
    public $table;
    public $group;
    public $joins;
    public $ignore = '';
    public $reset = true;
    public $debug = false;
    public $isColumnVal = false;
    public $stop;
    public $tmpTable;
    private $orderField;
    public $preAttribute = array();
    public $search = array('select', 'join', 'where', 'group', 'order', 'limit', 'offset');
    public function __construct($name = "")
    {
        $this->dbConfig = \Yaf\Application::app()->getConfig()->db;
        if (!empty($name)) {
            $this->name($name);
        }
    }
    /*****************************
     * orm参数配置区块 *
     *****************************/
    public function noReset()
    {
        $this->reset = false;
        return $this;
    }
    public function usePreSqlAttr()
    {
        foreach ($this->preAttribute as $k => $v) {
            $this->$k = $v;
        }
        return $this;
    }
    //该方法需要得到数据库赋值
    public function db()
    {
        if (!self::$db) {
            try {
                $config = $this->dbConfig;
                $dns = 'mysql:dbname=' . $config->database . ';host=' . $config->host;
                self::$db = new PDO($dns, $config->username, $config->password);
            } catch (PDOException $e) {
                dd('Connection failed: ' . $e->getMessage());
            }
        }
        return self::$db;
    }
    /**
     * 配置表名
     */
    public function name($table)
    {
        if (!empty($table)) {
            $this->tmpTable = $this->dbConfig->prefix . parse_name($table);
        }
        return $this;
    }
    /**
     * 配置where语句
     * @param type $where where数组
     * @param type $additional 重置where语句
     * @return $this
     */
    public function where($where, $additional = true)
    {
        if ($additional && !empty($this->where)) {
            $this->where = array_merge($this->where, $where);
        } else {
            $this->where = $where;
        }
        return $this;
    }
    /**
     * 配置组
     * @param type $group
     * @return $this
     */
    public function group($group)
    {
        $this->group = $group;
        return $this;
    }
    /**
     * 配置链表
     * @param type $table 表名
     * @param type $on 链表条件
     * @param type $columns 获取字段
     * @param type $type 链表方式
     * @return $this
     */
    public function join($table, $on, $columns = array(), $type = 'INNER')
    {
        $this->joins[] = array(
            'table' => $table,
            'on' => $on,
            'columns' => $columns,
            'type' => $type,
        );
        return $this;
    }
    /**
     * 配置获取当前表的列
     * @param array $columns 列名数组
     * @return $this
     */
    public function columns(array $columns)
    {
        $this->columns = $columns;
        return $this;
    }
    /**
     * 配置数据起点
     * @param int $num 多少条数据开始
     * @return $this
     */
    public function offset($num)
    {
        $this->offset = intval($num);
        return $this;
    }
    /**
     * 配置数据获取条数
     * @param int $num 获取数据条数
     * @return $this
     */
    public function limit($num)
    {
        $this->limit = intval($num);
        return $this;
    }
    /**
     * 配置排序规则
     * @param string $order 排序规则
     * @return $this
     */
    public function order($order)
    {
        $this->order = $order;
        return $this;
    }

    /*****************************
     * orm参数配置区块结束 *
     *****************************/

    public function produceSql()
    {
        foreach ($this->search as $v) {
            $process = 'process' . ucfirst($v);
            $sqlArr[] = method_exists($this, $process) ? call_user_func(array($this, $process)) : $this->replaceParam($v);
        }
        $sql = implode(' ', $sqlArr);
        $this->outputSql($sql);
        $this->initCondition();
        return $sql;
    }
    public function isColumnVal()
    {
        $this->isColumnVal = true;
        return $this;
    }
    //默认处理
    protected function replaceParam($k)
    {
        return isset($this->{$k}) && !empty($this->{$k}) ? sprintf($this->sqlFormar[$k], $this->{$k}) : '';
    }
    public function initCondition()
    {
        //是否重置配置
        if (!$this->reset) {
            $this->reset = true;
            return;
        }
        $this->preSqlAttribute('columns', array('*'));
        $this->preSqlAttribute('where');
        $this->preSqlAttribute('group');
        $this->preSqlAttribute('order');
        $this->preSqlAttribute('tmpTable');
        $this->preSqlAttribute('joins', array());
        $this->preSqlAttribute('isColumnVal', false);
        $this->preSqlAttribute('ignore');
        $this->preSqlAttribute('onUpdate');
        $this->preSqlAttribute('offset');
        $this->preSqlAttribute('limit');
    }
    public function preSqlAttribute($attr, $value = '')
    {
        $this->preAttribute[$attr] = $this->$attr;
        $this->$attr = $value;
        return $this;
    }
    public function processColumns($table, $columns)
    {
        $columnsList = array();
        $table = '`' . (is_array($table) ? key($table) : $table) . '`.';
        foreach ($columns as $k => $v) {
            if (empty($v)) {
                continue;
            }
            $vv = $v instanceof OrmExpression ? $v->expression : $table . $v;
            $columnsList[] = !is_numeric($k) ? $vv . ' `' . $k . '`' : $vv;
        }
        return implode(',', $columnsList);
    }
    public function getTable()
    {
        $table = $this->tmpTable ? $this->tmpTable : $this->table;
        if (empty($table)) {
            throw new Exception('no have table');
        }
        return $table;
    }
    public function getTableName()
    {
        $table = $this->getTable();
        return is_array($table) ? current($table) : $table;
    }
    public function processTable($table)
    {
        if (is_array($table)) {
            $alias = key($table);
            $realTable = current($table);
            $table = $realTable . ' AS ' . $alias;
        }
        return $table;
    }
    public function processSelect()
    {
        $columnList = array();
        if (!empty($this->joins) && !$this->isColumnVal) {
            foreach ($this->joins as $v) {
                if (!empty($v['columns'])) {
                    $columnList[] = $this->processColumns($v['table'], $v['columns']);
                }
            }
        }
        if (!empty($this->columns)) {
            $columnList[] = $this->processColumns($this->getTable(), $this->columns);
        }

        if (empty($columnList)) {
            throw new Exception('sql no have columns');
        }

        $column = implode(',', $columnList);
        $sql = sprintf(self::SELECT, $column, $this->processTable($this->getTable()));
        return $sql;
    }
    public function processWhere()
    {
        if (empty($this->where)) {
            return '';
        }
        if (is_array($this->where)) {
            $tmp = array();
            foreach ($this->where as $k => $v) {
                if (is_array($v)) {
                    if (empty($v)) {
                        continue;
                    }
                    $tmp[] = '`' . $v[0] . '`' . $v[1] . $v[2];
                    // $ids = implode(',', $v);
                    // $tmp[] = $k . ' in (' . $ids . ')';
                    // if ($this->orderField == $k) {
                    // $this->order('FIELD(' . $k . ',' . $ids . ')');
                    // }
                } else {
                    $tmp[] = $k . '="' . $v . '"';
                }
            }
            $tmpstr = implode(' AND ', $tmp);

        } else {
            $tmpstr = $this->where;
        }
        $sql = sprintf(self::WHERE, $tmpstr);
        return $sql;
    }
    public function orderField($field)
    {
        $this->orderField = $field;
        return $this;
    }
    public function processJoin()
    {
        $sql = array();
        if (!empty($this->joins)) {
            foreach ($this->joins as $v) {
                $sql[] = sprintf(self::JOIN, $v['type'], $this->processTable($v['table']));
                if (!empty($v['on'])) {
                    $sql[] = sprintf(self::JOIN_ON, $v['on']);
                }
            }
        }
        return implode(' ', $sql);
    }
    public function selectAll($where = array(), $table = false)
    {
        return $this->from($table)->where($where)->getAll();
    }
    public function selectOne($where, $table = '')
    {
        return $this->from($table)->where($where)->getRow();
    }
    public function selectCount($where, $table = '')
    {
        return $this->from($table)->where($where)->count();
    }
    public function selectColumnVal($column, $where, $table = '')
    {
        return $this->from($table)->where($where)->getColumnVal($column);
    }
    public function selectColumnList($column, $where, $table = '')
    {
        return $this->from($table)->where($where)->getColumnList($column);
    }
    public function getQuery($sql)
    {
        $query = $this->db()->query($sql);
        if (!is_object($query)) {
            throw new Exception($sql);
        }
        return $query;
    }
    public function query($sql)
    {
        return $this->db()->query($sql);
    }
    public function lastInsertId()
    {
        return $this->db()->lastInsertId();
    }
    public function commit()
    {
        return $this->db()->commit();
    }
    public function beginTransaction()
    {
        return $this->db()->beginTransaction();
    }
    public function rollBack()
    {
        return $this->db()->rollBack();
    }

    public function find()
    {
        $sql = $this->produceSql();
        $data = $this->getQuery($sql)->fetch(PDO::FETCH_ASSOC);
        return empty($data) ? array() : $data;
    }
    public function select()
    {
        $sql = $this->produceSql();
        $data = $this->getQuery($sql)->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }
    public function count()
    {
        //初始化条数限制
        $this->offset(0)->limit(0);
        if (!empty($this->group)) {
            $this->from(array('a' => '(' . $this->produceSql() . ')'));
        }
        return $this->getColumnVal('count(*)');
    }
    public function getColumnVal($column)
    {
        $row = $this->isColumnVal()->columns(array('columnVal' => new OrmExpression($column)))->getRow();
        return $row['columnVal'];
    }
    public function getColumnList($column)
    {
        $list = $this->isColumnVal()->columns(array('columnVal' => new OrmExpression($column)))->getAll();
        return empty($list) ? array() : array_column($list, 'columnVal');
    }
    public function onUpdate(array $update)
    {
        $this->onUpdate = $update;
        return $this;
    }
    //处理插入更新
    public function processOnUpdate()
    {
        if (empty($this->onUpdate)) {
            return '';
        }
        foreach ($this->onUpdate as $k => $v) {
            $sqlArr[] = is_numeric($k) ? "{$v}=VALUES({$v})" : "{$k}={$v}";
        }
        $sql = implode(',', $sqlArr);
        return sprintf(self::INSERT_UPDATE, $sql);
    }
    public function ignore($ignore)
    {
        $this->ignore = $ignore;
        return $this;
    }
    public function insert($info, $table = '')
    {
        $key = key($info);
        //处理批量插入数据
        if (is_numeric($key)) {
            $oneInfo = current($info);
            $columns = '`' . implode('`,`', array_keys($oneInfo)) . '`';
            $values = '';
            foreach ($info as $v) {
                $values .= "('" . implode("','", array_map('addslashes', $v)) . "'),";
            }
            $values = rtrim($values, ',');
        } else {
            $columns = '`' . implode('`,`', array_keys($info)) . '`';
            $values = "('" . implode("','", array_map('addslashes', $info)) . "')";
        }
        $sql = sprintf(self::INSERT, $this->ignore, $this->from($table)->getTableName(), $columns, $values) . ' ' . $this->processOnUpdate();
        $this->outputSql($sql);
        $this->initCondition();
        return $this->db()->exec($sql);
    }

    public function update($info, $where = array(), $table = '')
    {
        foreach ($info as $k => $v) {
            $values[] = $v instanceof OrmExpression ? "`{$k}`={$v->expression}" : "`{$k}`='{$v}'";
        }
        $sql = sprintf(self::UPDATE, $this->from($table)->getTableName(), implode(',', $values)) . ' ' . $this->where($where)->processWhere();
        $this->outputSql($sql);
        $this->initCondition();
        return $this->db()->exec($sql);
    }
    public function delete($where = array(), $table = '')
    {
        $sql = sprintf(self::DELETE, $this->from($table)->getTableName()) . ' ' . $this->where($where)->processWhere();
        $this->outputSql($sql);
        $this->initCondition();
        return $this->db()->exec($sql);
    }
    public function debug($stop = true)
    {
        $this->debug = true;
        $this->stop = $stop;
        return $this;
    }
    public function outputSql($sql)
    {
        if ($this->stop) {
            echo $sql;exit;
        }
        if ($this->debug) {
            $this->sqlLog[] = $sql;
        }
    }
    public function log()
    {
        return $this->sqlLog;
    }
}

class OrmExpression
{
    public $expression;
    public function __construct($expression)
    {
        $this->expression = $expression;
    }
}
